﻿using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace ConfigInfoWebPlatform.API.DBCommon
{
	/// <summary>
	/// sqlite数据库封装类
	/// </summary>
	public class SqliteHelper
	{
		private static string connectionString = string.Empty;

		public SqliteHelper(string litedbfile) 
		{
			this.SetConnectionString(litedbfile, "");
		}


		/// <summary>
		/// 根据数据源、密码、版本号设置连接字符串。
		/// </summary>
		/// <param name="datasource">数据源。</param>
		/// <param name="password">密码。</param>
		/// <param name="version">版本号（缺省为3）。</param>
		public void SetConnectionString(string datasource, string password)
		{
			string str = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"\";
			connectionString = string.Format("Data Source={0};",
				str + datasource);
		}

		///// <summary>
		///// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。
		///// </summary>
		///// <param name="dbName">数据库文件名。为null或空串时不创建。</param>
		//public void CreateDB(string dbName)
		//{
		//	if (!string.IsNullOrEmpty(dbName))
		//	{
		//		try { SqliteConnection.CreateFile(dbName); }
		//		catch (Exception) { throw; }
		//	}
		//}

		/// <summary> 
		/// 对SQLite数据库执行增删改操作，返回受影响的行数。 
		/// </summary> 
		/// <param name="sql">要执行的增删改的SQL语句。</param> 
		/// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
		/// <returns></returns> 
		public int ExecuteNonQuery(string sql, params SqliteParameter[] parameters)
		{
			int affectedRows = 0;
			using (SqliteConnection connection = new SqliteConnection(connectionString))
			{
				using (SqliteCommand command = new SqliteCommand(sql, connection))
				{
					try
					{
						connection.Open();
						//command.CommandText = sql;
						if (parameters.Length != 0)
						{
							command.Parameters.AddRange(parameters);
						}
						affectedRows = command.ExecuteNonQuery();
					}
					catch (Exception ex)
					{
						Console.WriteLine(ex.Message);
					}
				}
			}
			return affectedRows;
		}

		///// <summary>
		///// 批量处理数据操作语句。
		///// </summary>
		///// <param name="list">SQL语句集合。</param>
		//public void ExecuteNonQueryBatch(List<KeyValuePair<string, SqliteParameter[]>> list)
		//{
		//	using (SqliteConnection conn = new SqliteConnection(connectionString))
		//	{
		//		try { conn.Open(); }
		//		catch { throw; }
		//		using (SqliteTransaction tran = conn.BeginTransaction())
		//		{
		//			using (SqliteCommand cmd = new SqliteCommand(conn))
		//			{
		//				try
		//				{
		//					foreach (var item in list)
		//					{
		//						cmd.CommandText = item.Key;
		//						if (item.Value != null)
		//						{
		//							cmd.Parameters.AddRange(item.Value);
		//						}
		//						cmd.ExecuteNonQuery();
		//					}
		//					tran.Commit();
		//				}
		//				catch (Exception) { tran.Rollback(); throw; }
		//			}
		//		}
		//	}
		//}

		/// <summary>
		/// 执行查询语句，并返回第一个结果。
		/// </summary>
		/// <param name="sql">查询语句。</param>
		/// <returns>查询结果。</returns>
		public object ExecuteScalar(string sql, params SqliteParameter[] parameters)
		{
			using (SqliteConnection conn = new SqliteConnection(connectionString))
			{
				using (SqliteCommand cmd = new SqliteCommand(sql,conn))
				{
					try
					{
						conn.Open();
						if (parameters.Length != 0)
						{
							cmd.Parameters.AddRange(parameters);
						}
						return cmd.ExecuteScalar();
					}
					catch (Exception) { throw; }
				}
			}
		}

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable。 
        /// </summary> 
        /// <param name="sql">要执行的查询语句。</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        public DataTable ExecuteQuery(string sql, params SqliteParameter[] parameters)
        {
            using (SqliteConnection connection = new SqliteConnection(connectionString))
            {
                using (SqliteCommand command = new SqliteCommand(sql, connection))
                {
                    if (parameters.Length != 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
					connection.Open();
					 var ireader=command.ExecuteReader();
					DataTable data = new DataTable();
					data.Load(ireader);
					//SqliteDataAdapter adapter = new SQLiteDataAdapter(command);
					//try { adapter.Fill(data); }
     //               catch (Exception) { throw; }
                    return data;
                }
            }
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。 
        /// </summary> 
        /// <param name="sql">要执行的查询语句。</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param> 
        /// <returns></returns> 
        public SqliteDataReader ExecuteReader(string sql, params SqliteParameter[] parameters)
		{
			SqliteConnection connection = new SqliteConnection(connectionString);
			SqliteCommand command = new SqliteCommand(sql, connection);
			try
			{
				if (parameters.Length != 0)
				{
					command.Parameters.AddRange(parameters);
				}
				connection.Open();
				return command.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception) { throw; }
		}

		/// <summary> 
		/// 查询数据库中的所有数据类型信息。
		/// </summary> 
		/// <returns></returns> 
		public DataTable GetSchema()
		{
			using (SqliteConnection connection = new SqliteConnection(connectionString))
			{
				try
				{
					connection.Open();
					return connection.GetSchema("TABLES");
				}
				catch (Exception) { throw; }
			}
		}
	}
}
